package com.ffms.dao.impl;

import com.ffms.dao.BankDao;
import com.ffms.entity.Bank;
import com.ffms.entity.FamilyMember;
import com.ffms.entity.ZhiChu;
import com.ffms.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 从卿
 * @create 2022/3/8-1:27
 */
public class BankDaoImpl implements BankDao {
    @Override
    public int save(Bank bank) {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "insert into tables.t_bank values (b_id,?,?,?)";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);

            Object[] prams = {bank.getBname(), bank.getBmoney(), bank.getMid()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);

            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;

    }

    @Override
    public List<Bank> findBank() {
        List<Bank> bankList = new ArrayList<Bank>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.* from tables.t_bank sh join tables.t_member tm on tm.sid = sh.mid ";
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("b_id");
                String bname = rs.getString("b_name");
                String money = rs.getString("b_money");
                Bank bank = new Bank(id, bname, money);
                int sid = rs.getInt("sid");

                String name = rs.getString("name");

                FamilyMember fm = new FamilyMember(sid, name);
                bank.setFamilyMember(fm);
                //  将对象放入集合
                bankList.add(bank);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return bankList;
    }

    @Override
    public void deletBankById(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "delete from t_bank where b_id =?";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {id};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Override
    public Bank getById(int id) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Bank bank = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select  * from  t_bank where  b_id =" + id;
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                int thisId = rs.getInt("b_id");
                String name = rs.getString("b_name");
                String money = rs.getString("b_money");
                bank = new Bank(thisId, name, money);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return bank;

    }

    @Override
    public int updateBank(Bank bank) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "update t_bank set `b_name` =? ,b_money = ? where b_id=? ";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {bank.getBname(), bank.getBmoney(), bank.getBid()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }

    @Override
    public List<Bank> findBankByMid(int mid) {
        List<Bank> bankList = new ArrayList<Bank>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.* from tables.t_bank sh join tables.t_member tm on tm.sid = sh.mid  where sh.mid = " + mid;
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("b_id");
                String bname = rs.getString("b_name");
                String money = rs.getString("b_money");
                Bank bank = new Bank(id, bname, money);
                int sid = rs.getInt("sid");

                String name = rs.getString("name");

                FamilyMember fm = new FamilyMember(sid, name);
                bank.setFamilyMember(fm);
                //  将对象放入集合
                bankList.add(bank);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return bankList;
    }
    //通过t_bank mid找到账户

    public Bank getByMId(String mid) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Bank bank = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select  * from tables.t_bank where  mid =" + mid;
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                int thisId = rs.getInt("b_id");
                String name = rs.getString("b_name");
                String money = rs.getString("b_money");
                String thisMid = rs.getString("mid");
                bank = new Bank(thisId, name, money, thisMid);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return bank;
    }
   
}
